package com.education.ai.dao.impl;

import com.education.ai.dao.ExerciseDao;
import com.education.ai.entity.Exercise;
import com.education.ai.util.JdbcUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 练习题数据访问实现类
 */
@Repository
public class ExerciseDaoImpl implements ExerciseDao {

    private static final Logger logger = LoggerFactory.getLogger(ExerciseDaoImpl.class);
    
    @Autowired
    private JdbcUtil jdbcUtil;
    
    @Override
    public List<Exercise> findAll() {
        String sql = "SELECT * FROM exercises";
        return mapResultsToExercises(jdbcUtil.executeQuery(sql));
    }
    
    @Override
    public Optional<Exercise> findById(Long id) {
        String sql = "SELECT * FROM exercises WHERE id = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, id);
        return results.isEmpty() ? Optional.empty() : Optional.of(mapRowToExercise(results.get(0)));
    }
    
    @Override
    public List<Exercise> findBySubject(String subject) {
        String sql = "SELECT * FROM exercises WHERE subject = ?";
        return mapResultsToExercises(jdbcUtil.executeQuery(sql, subject));
    }
    
    @Override
    public List<Exercise> findByGrade(String grade) {
        String sql = "SELECT * FROM exercises WHERE grade = ?";
        return mapResultsToExercises(jdbcUtil.executeQuery(sql, grade));
    }
    
    @Override
    public List<Exercise> findByTopic(String topic) {
        String sql = "SELECT * FROM exercises WHERE topic = ?";
        return mapResultsToExercises(jdbcUtil.executeQuery(sql, topic));
    }
    
    @Override
    public List<Exercise> findByQuestionType(String questionType) {
        String sql = "SELECT * FROM exercises WHERE question_type = ?";
        return mapResultsToExercises(jdbcUtil.executeQuery(sql, questionType));
    }
    
    @Override
    public List<Exercise> findByDifficulty(String difficulty) {
        String sql = "SELECT * FROM exercises WHERE difficulty = ?";
        return mapResultsToExercises(jdbcUtil.executeQuery(sql, difficulty));
    }
    
    @Override
    public List<Exercise> findByTeacherId(String teacherId) {
        String sql = "SELECT * FROM exercises WHERE teacher_id = ?";
        return mapResultsToExercises(jdbcUtil.executeQuery(sql, teacherId));
    }
    
    @Override
    public List<Exercise> findBySubjectAndGrade(String subject, String grade) {
        String sql = "SELECT * FROM exercises WHERE subject = ? AND grade = ?";
        return mapResultsToExercises(jdbcUtil.executeQuery(sql, subject, grade));
    }
    
    @Override
    public List<Exercise> findBySubjectAndGradeAndDifficulty(String subject, String grade, String difficulty) {
        String sql = "SELECT * FROM exercises WHERE subject = ? AND grade = ? AND difficulty = ?";
        return mapResultsToExercises(jdbcUtil.executeQuery(sql, subject, grade, difficulty));
    }
    
    @Override
    public List<Exercise> findBySubjectAndGradeAndTopic(String subject, String grade, String topic) {
        String sql = "SELECT * FROM exercises WHERE subject = ? AND grade = ? AND topic = ?";
        return mapResultsToExercises(jdbcUtil.executeQuery(sql, subject, grade, topic));
    }
    
    @Override
    public List<Exercise> findWithPagination(int offset, int limit) {
        String sql = "SELECT * FROM exercises LIMIT ? OFFSET ?";
        return mapResultsToExercises(jdbcUtil.executeQuery(sql, limit, offset));
    }
    
    @Override
    public Exercise save(Exercise exercise) {
        if (exercise.getId() == null) {
            // 插入操作
            String sql = "INSERT INTO exercises (subject, grade, topic, question_type, content, answer, " +
                    "analysis, difficulty, teacher_id, created_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            // 设置创建时间
            if (exercise.getCreatedTime() == null) {
                exercise.setCreatedTime(LocalDateTime.now());
            }
            
            long id = jdbcUtil.executeInsert(sql, 
                    exercise.getSubject(), 
                    exercise.getGrade(), 
                    exercise.getTopic(), 
                    exercise.getQuestionType(), 
                    exercise.getContent(), 
                    exercise.getAnswer(),
                    exercise.getAnalysis(),
                    exercise.getDifficulty(),
                    exercise.getTeacherId(),
                    Timestamp.valueOf(exercise.getCreatedTime()));
            
            exercise.setId(id);
            return exercise;
        } else {
            // 更新操作
            update(exercise);
            return exercise;
        }
    }
    
    @Override
    public List<Exercise> saveAll(List<Exercise> exercises) {
        if (exercises == null || exercises.isEmpty()) {
            return Collections.emptyList();
        }
        
        List<Exercise> savedExercises = new ArrayList<>();
        for (Exercise exercise : exercises) {
            savedExercises.add(save(exercise));
        }
        
        return savedExercises;
    }
    
    @Override
    public boolean update(Exercise exercise) {
        String sql = "UPDATE exercises SET subject = ?, grade = ?, topic = ?, question_type = ?, content = ?, " +
                "answer = ?, analysis = ?, difficulty = ?, teacher_id = ? WHERE id = ?";
        
        int rows = jdbcUtil.executeUpdate(sql,
                exercise.getSubject(),
                exercise.getGrade(),
                exercise.getTopic(),
                exercise.getQuestionType(),
                exercise.getContent(),
                exercise.getAnswer(),
                exercise.getAnalysis(),
                exercise.getDifficulty(),
                exercise.getTeacherId(),
                exercise.getId());
        
        return rows > 0;
    }
    
    @Override
    public boolean deleteById(Long id) {
        String sql = "DELETE FROM exercises WHERE id = ?";
        int rows = jdbcUtil.executeUpdate(sql, id);
        return rows > 0;
    }
    
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) AS count FROM exercises";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql);
        if (!results.isEmpty()) {
            return ((Number) results.get(0).get("count")).longValue();
        }
        return 0;
    }
    
    @Override
    public long countBySubject(String subject) {
        String sql = "SELECT COUNT(*) AS count FROM exercises WHERE subject = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, subject);
        if (!results.isEmpty()) {
            return ((Number) results.get(0).get("count")).longValue();
        }
        return 0;
    }
    
    @Override
    public long countBySubjectAndGrade(String subject, String grade) {
        String sql = "SELECT COUNT(*) AS count FROM exercises WHERE subject = ? AND grade = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, subject, grade);
        if (!results.isEmpty()) {
            return ((Number) results.get(0).get("count")).longValue();
        }
        return 0;
    }
    
    /**
     * 将查询结果映射为练习题对象列表
     */
    private List<Exercise> mapResultsToExercises(List<Map<String, Object>> results) {
        List<Exercise> exercises = new ArrayList<>();
        for (Map<String, Object> row : results) {
            exercises.add(mapRowToExercise(row));
        }
        return exercises;
    }
    
    /**
     * 将单行结果映射为练习题对象
     */
    private Exercise mapRowToExercise(Map<String, Object> row) {
        Exercise exercise = new Exercise();
        exercise.setId(((Number) row.get("id")).longValue());
        exercise.setSubject((String) row.get("subject"));
        exercise.setGrade((String) row.get("grade"));
        exercise.setTopic((String) row.get("topic"));
        exercise.setQuestionType((String) row.get("question_type"));
        exercise.setContent((String) row.get("content"));
        exercise.setAnswer((String) row.get("answer"));
        exercise.setAnalysis((String) row.get("analysis"));
        exercise.setDifficulty((String) row.get("difficulty"));
        exercise.setTeacherId((String) row.get("teacher_id"));
        
        // 处理日期时间类型
        if (row.get("created_time") != null) {
            Object timeObj = row.get("created_time");
            if (timeObj instanceof java.sql.Timestamp) {
                exercise.setCreatedTime(((java.sql.Timestamp) timeObj).toLocalDateTime());
            } else if (timeObj instanceof LocalDateTime) {
                exercise.setCreatedTime((LocalDateTime) timeObj);
            } else if (timeObj instanceof java.util.Date) {
                // 将java.util.Date转换为LocalDateTime
                java.util.Date date = (java.util.Date) timeObj;
                exercise.setCreatedTime(new java.sql.Timestamp(date.getTime()).toLocalDateTime());
            } else {
                logger.warn("无法识别的创建时间类型: {}, 值: {}", timeObj.getClass().getName(), timeObj);
            }
        }
        
        return exercise;
    }
} 